<?php 
$PageSecurity = 11;
include_once ('includes/session.inc');
set_time_limit(0);

$timeWait = 40000;
$excelType = 'Excel5'; // default Excel 2003 - Excel5,Excel2007
$isCanUpload = false;
$affectRecord = 0;
$failRecord = 0;
$fails = '-';
$sodongloi = 0;
$dongloi = '';
$sobailoi = 0;
$baihatloi = '';
$tongsoloi = 0;
$content = array('Kho bài hát', 'Đang cập nhật...', 'Đã import', 'hoàn thành', 'lỗi', 'dòng', 'Quá trình upload bị lỗi.', 'Vui lòng chọn loại tập tin (*.xls, *.xlsx).');

$sheetIndex = !isset($_REQUEST['SheetIndex']) ? 0 : $_REQUEST['SheetIndex'];
$startRow = !isset($_REQUEST['StartRow']) ? 2 : $_REQUEST['StartRow'];
$partnerId = !isset($_REQUEST['hiddenPartnerId']) ? 2 : $_REQUEST['hiddenPartnerId'];

$uploaddir = 'tmp/imported/';
$currDate = getdate();

$action = isset($_POST['action']) ? $_POST['action'] : 'import';

if ($action == 'getPartner') {
    $result = _single('api/partner')->GetAllPartner($_SESSION['UserID'], $_SESSION['Password']);
	
    $data = '{"success":true';
    $data .= ',"data":[';
    $haveData = false;
    
    while ($row = DB_fetch_array($result)) {
        $haveData = true;
        $data .= sprintf('{"partner_id":"%s","partner_number":"%s","partner_name":"%s"},', $row['partner_id'], $row['partner_number'], $row['partner_name']);
    }
    
    if ($haveData) {
        $data = substr($data, 0, strlen($data) - 1);
    }
    
    $data .= '],"count":"'.json_encode($total).'"}';
    echo $data;
} elseif ($action == 'import') {
	if (file_exists($_FILES['fileupload']['tmp_name'])) {
		if ($_FILES['fileupload']['type'] == "application/vnd.ms-excel" || ($_FILES['fileupload']['type'] == "application/x-msexcel") ||  
		($_FILES['fileupload']['type'] == "application/excel")) {
			$nameUploadFile = $currDate['0'].basename($_FILES['fileupload']['name']);
			$isCanUpload = true;
		} else if ($_FILES['fileupload']['type'] == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") {
			$nameUploadFile = $currDate['0'].basename($_FILES['fileupload']['name']);
			$isCanUpload = true;
			$excelType = 'Excel2007';
		}
		
		if ($isCanUpload) {
			if (move_uploaded_file($_FILES['fileupload']['tmp_name'], $uploaddir.$nameUploadFile)) {
				$ajaxProgress = new Api_Ajax();
				
				$objPHPExcel = new Api_Excel($nameUploadFile, $uploaddir, $excelType);
				$objPHPExcel->loadIOFactory();
				$objPHPExcel->setActiveSheet($sheetIndex);
				$numberRow = $objPHPExcel->getRowCount();
				
				if(!isset($_SESSION['MaxObjectNumber']) || !isset($_SESSION['DefaultPriceOfSong'])){
					$ForceConfigReload = true;
					include($PathPrefix . 'includes/GetConfig.php');
				}

				for ($rowIndex = $startRow; $rowIndex <= $numberRow; $rowIndex++) {
					// time start importing a record
					$time_start = microtime_float();
					// dung cho truong hp dau tien khi import
					$code = trim($objPHPExcel->getCellByColumnAndRow(1, $rowIndex));				
					if(!is_float($code)){
						$i = 0;
						while ($char = $code[$i++]) {
							if(intval($char) != 0) break;
						}
						$code = substr($code, --$i);
					}
					$singer = trim($objPHPExcel->getCellByColumnAndRow(4, $rowIndex));
					$composer = trim($objPHPExcel->getCellByColumnAndRow(5, $rowIndex));
					
					$songName = trim($objPHPExcel->getCellByColumnAndRow(2, $rowIndex));
					$songUnsignedName = trim($objPHPExcel->getCellByColumnAndRow(3, $rowIndex));
					$songSingerAuthority = trim($objPHPExcel->getCellByColumnAndRow(6, $rowIndex));
					$songComposerAuthority = trim($objPHPExcel->getCellByColumnAndRow(7, $rowIndex));
					$songReleaseAuthority = trim($objPHPExcel->getCellByColumnAndRow(8, $rowIndex));
					
					$createDate = $endDate = date('m/d/Y');	
					$createDate = excel_date(intval(trim($objPHPExcel->getCellByColumnAndRow(9, $rowIndex))), 'm/d/Y');
					$endDate = excel_date(intval(trim($objPHPExcel->getCellByColumnAndRow(10, $rowIndex))), 'm/d/Y');
						
					$singerId = ProgressObject($singer, 1);
					$composerId = ProgressObject($composer, 2);

					if(!empty($songName)){
						$SongDetails = array(
							'type_id' => 1,
							'partner_id' => $partnerId,
							'singer_id' => $singerId,
							'composer_id' => $composerId,
							'song_number' => $code,
							'song_name' => $songName,
							'song_unsigned_name' => $songUnsignedName,
							'song_price' => $_SESSION['DefaultPriceOfSong'],
							'song_singer_sing' => $singer,
							'song_singer_authority' => !empty($songSingerAuthority)?$songSingerAuthority:0,
							'song_composer_authority' => !empty($songComposerAuthority)?$songComposerAuthority:0,
							'song_release_authority' => !empty($songReleaseAuthority)?$songReleaseAuthority:0,
							'ended_date' => $endDate,
							'created_date' => $createDate,
							'created_by' => $_SESSION['UserID'],
							'updated_date' => date('m/d/Y'),
							'updated_by' => $_SESSION['UserID']);

						if(!empty($code)){
							$song = _single('api/song')->GetSongByNumber($code, $partnerId, $_SESSION['UserID'], $_SESSION['Password']);	
							if(sizeof($song) != 0){
								// cap nhat lai ca khuc neu da co ma roi	
								$Result = _single('api/song')->ModifySong($song['song_id'], $SongDetails, $_SESSION['UserID'], $_SESSION['Password']);
							}else{
								// neu khong co ma so bai hat thi insert moi vao
								$Result = _single('api/song')->InsertSong($SongDetails, $_SESSION['UserID'], $_SESSION['Password']);
							}
							if(sizeof($Result) == 0){
								$affectRecord++;
							}else{
								$tongsoloi++;
								$sobailoi++;
								if($sobailoi <= 100){
									$baihatloi .= $code.'-';
								}
							}
						}			
					}else{
						$tongsoloi++;
						$sodongloi++;
						$dongloi .= $rowIndex.'-';
					}
					/*
					// dung cho sau nay import du lieu
					$SongCode = trim($objPHPExcel->getCellByColumnAndRow(2, $rowIndex));				
					if(!is_float($SongCode)){
						$i = 0;
						while ($char = $SongCode[$i++]) {
							if(intval($char) != 0) break;
						}
						$SongCode = substr($SongCode, --$i);
					}
					$SingerCode = trim($objPHPExcel->getCellByColumnAndRow(5, $rowIndex));
					$SingerName = trim($objPHPExcel->getCellByColumnAndRow(6, $rowIndex));
					$ComposerCode = trim($objPHPExcel->getCellByColumnAndRow(7, $rowIndex));
					$ComposerName = trim($objPHPExcel->getCellByColumnAndRow(8, $rowIndex));				
					$ObjectSinger = _single('api/object')->GetObjectByNumber($SingerCode, $_SESSION['UserID'], $_SESSION['Password']);
					$ObjectComposer = _single('api/object')->GetObjectByNumber($ComposerCode, $_SESSION['UserID'], $_SESSION['Password']);
					
					$SongName = trim($objPHPExcel->getCellByColumnAndRow(3, $rowIndex));
					$SongUnsignedName = trim($objPHPExcel->getCellByColumnAndRow(4, $rowIndex));
					$SongSingerAuthority = trim($objPHPExcel->getCellByColumnAndRow(9, $rowIndex));
					$SongComposerAuthority = trim($objPHPExcel->getCellByColumnAndRow(10, $rowIndex));
					
					$SingerId = FindObject($ObjectSinger, $SingerCode, $SingerName, 1);
					$ComposerId = FindObject($ObjectComposer, $ComposerCode, $ComposerName, 2);
						
					if(!empty($SongCode) && isset($SingerId) && isset($ComposerId)){
						$SongDetails = array(
							'type_id' => 1,
							'partner_id' => 2,
							'singer_id' => $SingerId,
							'composer_id' => $ComposerId,
							'song_number' => $SongCode,
							'song_name' => $SongName,
							'song_unsigned_name' => $SongUnsignedName,
							'song_price' => $_SESSION['DefaultPriceOfSong'],
							'song_singer_authority' => !empty($SongSingerAuthority)?$SongSingerAuthority:0,
							'song_composer_authority' => !empty($SongComposerAuthority)?$SongComposerAuthority:0,
							'updated_date' => date('m/d/Y'),
							'updated_by' => $_SESSION['UserID']);

						$song = _single('api/song')->GetSongByNumber($SongCode, $_SESSION['UserID'], $_SESSION['Password']);	
						if(sizeof($song) != 0){						
							$Result = _single('api/song')->ModifySong($song['song_id'], $SongDetails, $_SESSION['UserID'], $_SESSION['Password']);					
						}else{
							// neu khong co ma so bai hat thi insert moi vao
							$SongDetails['created_date'] = date('Y-m-d');
							$SongDetails['created_by'] = $_SESSION['UserID'];
							$Result = _single('api/song')->InsertSong($SongDetails, $_SESSION['UserID'], $_SESSION['Password']);
						}
						if(sizeof($Result) == 0){
							$affectRecord++;
						}else{
							$tongsoloi++;
							$sobailoi++;
							$baihatloi .= $SongCode.'-';
						}
					}else{
						$tongsoloi++;
						$sodongloi++;
						$dongloi .= $rowIndex.'-';
					}
					$SongCode .= ' - Ca sỹ: '.$ObjectSinger['object_name'].' nhạc Sỹ: '.$ObjectComposer['object_name'];
					$msgTitle = "$content[0]: $SongCode";
					*/
					
					$msgTitle = "$content[0]: $code - $songName";
					
					$ajaxProgress->advance(($rowIndex + 1) / $numberRow, "$content[1] $rowIndex/$numberRow", $msgTitle);
					// time delay
					$time_end = microtime_float();
					$time = ($time_end - $time_start) * 1000000; // convert second into milisecond
					if ($time < $timeWait)
						usleep($timeWait - $time);
				}
				$ajaxProgress = null;
				echo '{success:true, msg:'.json_encode(
					array(
						'content'=>"$content[2] $content[3]: $affectRecord - $content[4] $content[5] : $tongsoloi",
						'affectRecord'=>"$affectRecord",
						'tongsoloi'=>"$tongsoloi",
						'sodongloi'=>"$sodongloi",
						'dongloi'=>"$dongloi",
						'sobailoi'=>"$sobailoi",
						'baihatloi'=>"$baihatloi"
					)
				).'}';
				exit;
			} else {
				echo '{success:false, msg:'.json_encode($_FILES['fileupload']['name']).'}';
				exit;
			}
		}
	} else {
		echo '{success:false, msg:'.json_encode($_FILES['fileupload']['name'].' không tồn tại.').'}';
		exit;
	}
}

function microtime_float() {
    list($usec, $sec) = explode(" ", microtime());
    return ((float) $usec + (float) $sec);
}

function ProgressObject($Object, $Type) {
    $arrObject = array();
    
	$status = true;
    if (!strpos($Object, "X-band") && ($Object != "X-band") && 
	!strpos($Object, "LK") && ($Object != "LK")) {
        $arrObject = explodeX(array("&", ",", "-", "Ft", "ft"), $Object);
    }
    if (count($arrObject) > 1) {
        // neu la dang mang nhieu ca sy hay nhac sy chung
		$i = 0;
        foreach ($arrObject as $value) {
        	if($i == 0){
        		$objectId = Progress(trim($value), $Type);
        	}else{
        		Progress(trim($value), $Type);
        	}
        	$i++;
        }
    } else {
    	$objectId = Progress(trim($Object), $Type);
    }
    return $objectId;
}

function Progress($Object, $Type) {
	$Errors = array();
    $Result = _single('api/object')->GetObjectByName($Object, $_SESSION['UserID'], $_SESSION['Password']);
	$Errors[0] = DatabaseInsertFailed;
	if(!empty($Result)){
		// truong hop xet ca sy neu da ton tai ma la nhac sy thi cap nhat loai ca hai
		if((($Type == 1) && ($Result['object_type'] == 2)) || 
		(($Type == 2) && ($Result['object_type'] == 1))){
			$ObjectDetails = array(
				'field' => 'object_type',
				'value' => 3,
				'object_id' => $Result['object_id']
			);
			_single('api/object')->update($ObjectDetails, $_SESSION['UserID'], $_SESSION['Password']);
		}
		return $Result['object_id'];
	}else{
		$ObjectDetails = array(
			'object_number' => '',
			'object_name' => $Object,
			'object_company' => '',
			'object_type' => $Type,
			'created_date' => date('Y-m-d'),
			'created_by' => $_SESSION['UserID'],
			'updated_date' => date('Y-m-d'),
			'updated_by' => $_SESSION['UserID']
		);
		$Errors = _single('api/object')->InsertObject($ObjectDetails, $_SESSION['UserID'], $_SESSION['Password']);
		if(sizeof($Errors) == 0){
			return $_SESSION['LastInsertId'];
		}else{
			return null;
		}
	}
	
}
function FindObject($Object, $ObjectCode, $ObjectName, $type) {

	if(empty($ObjectCode) || !isset($Object)){	
		if(empty($ObjectName)){
			return null;
		}
		$ObjectDetails = array(
			'object_number' => $ObjectCode,
			'object_name' => $ObjectName,
			'object_real_name' => $ObjectName,
			'object_type' => $type,						
			'updated_date' => date('Y-m-d'),
			'updated_by' => $_SESSION['UserID']
		);
		$Object = _single('api/object')->GetObjectByName($ObjectName, $_SESSION['UserID'], $_SESSION['Password']);
		if(sizeof($Object) != 0){
			$Result = _single('api/object')->ModifyObject($Object['object_id'], $ObjectDetails, $_SESSION['UserID'], $_SESSION['Password']);
			return $Object['object_id'];
		}else{
			$ObjectDetails['created_date'] = date('Y-m-d');
			$ObjectDetails['created_by'] = $_SESSION['UserID'];
			$Result = _single('api/object')->InsertObject($ObjectDetails, $_SESSION['UserID'], $_SESSION['Password']);
			return $_SESSION['LastInsertId'];
		}
	}else{
		return $Object['object_id'];
	}
	return null;
}
?>
